package cn.isjinhao.se.script.parameter;

import cn.isjinhao.se.script.ScriptException;
import cn.isjinhao.se.structure.impl.ColumnMetaInfo;
import cn.isjinhao.se.structure.impl.Table;
import cn.isjinhao.se.structure.impl.TableMetaInfo;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author ISJINHAO
 * @Date 2021/6/30 9:36
 */
public class DefaultParameterReader implements ParameterReader {

    public TableMetaInfo extractColumnMetaInfoFromRow(Row row) {
        short lastCellNum = row.getLastCellNum();
        TableMetaInfo tableMetaInfo = new TableMetaInfo();
        for (short i = 0; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {
                throw new ScriptException("列名不能为空！");
            }
            String cellValueAsColumnName = getCellValueAsColumnName(cell);
            if (cellValueAsColumnName != null) {
                cellValueAsColumnName = getCellValueAsColumnName(cell).trim();
                if (cellValueAsColumnName.length() > 0) {
                    tableMetaInfo.append(new ColumnMetaInfo(cellValueAsColumnName, getCellTypeAsColumnType(cell)));
                }
            }
        }
        return tableMetaInfo;
    }

    // 这个格式是符合 MySQL insert 要求的
    private static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    public Object getCellValue(Cell cell) {
        Object cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case BLANK:
                cellValue = "";
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                DataFormatter dataFormatter = new DataFormatter();
                cellValue = dataFormatter.formatCellValue(cell);
                break;
            // 空值
            case NUMERIC:
                // 对于日期类型
                if (DateUtil.isCellDateFormatted(cell)) {
                    LocalDateTime dateCellValue = getDateCellValue(cell);
                    cellValue = DATE_TIME_FORMATTER.format(dateCellValue);
                } else {
                    cellValue = cell.getNumericCellValue();
                }
                break;
            case FORMULA:
                // 对于日期类型
                if (DateUtil.isCellDateFormatted(cell)) {
                    LocalDateTime dateCellValue = getDateCellValue(cell);
                    cellValue = DATE_TIME_FORMATTER.format(dateCellValue);
                } else {
                    cellValue = cell.getRichStringCellValue();
                }
                break;
            case ERROR:
                throw new ScriptException("Excel 文件有问题，位置：" + cell.getAddress());
            default:
                cellValue = "未知类型&未知数据";
                break;
        }
        return cellValue;
    }

    private String getCellValueAsColumnName(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            // 数字，字符串，函数，布尔类型全转化为字符串
            case NUMERIC:
            case STRING:
                DataFormatter dataFormatter = new DataFormatter();
                cellValue = dataFormatter.formatCellValue(cell);
                break;
            default:
                throw new ScriptException("列名必须为字符串类型或数值类型");
        }
        return cellValue;
    }

    private static Class<?> getCellTypeAsColumnType(Cell cell) {
        Class<?> clazz;
        switch (cell.getCellType()) {
            case NUMERIC:
                clazz = Double.class;
                break;
            case STRING:
                clazz = String.class;
                break;
            default:
                throw new ScriptException("列名必须为字符串类型或数值类型");
        }
        return clazz;
    }

    private static final LocalDateTime BASE_DATE = LocalDateTime.parse("1900-01-01T00:00:00");

    /**
     * 不支持 1904开始的日期。即默认所有的日期都是从1900/01/01开始的
     *
     * @param cell
     * @return
     */
    public static LocalDateTime getDateCellValue(Cell cell) {
        CellType cellType = cell.getCellType();
        if (cellType == CellType.ERROR) {
            throw new ScriptException("当前单元格类型错误：" + cell.getAddress());
        } else if (cellType == CellType.BLANK) {
            return null;
        } else if (cellType == CellType.NUMERIC || cellType == CellType.FORMULA) {
            double value;
            try {
                value = cell.getNumericCellValue();
            } catch (IllegalStateException e) {
                throw new ScriptException("无法从非日期类型的单元格获取到日期类型数据！");
            }
            return DateUtil.getLocalDateTime(value, false);
        } else {
            throw new ScriptException("当前单元格类型错误，能解析的单元格类型为NUMERIC和FORMULA：" + cell.getAddress());
        }
    }

    @Override
    public Table readFromXlsxFile(File file, int sheetNo) {

        Workbook sheets;
        try {
            sheets = WorkbookFactory.create(file);
        } catch (IOException e) {
            e.printStackTrace();
            return new Table();
        }

        Sheet sheet = sheets.getSheetAt(sheetNo);

        TableMetaInfo tableMetaInfo = null;
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum > 0) {
            Row row = sheet.getRow(0);
            tableMetaInfo = extractColumnMetaInfoFromRow(row);
        }

        if (tableMetaInfo == null || tableMetaInfo.size() == 0) {
            return new Table();
        }
        Table table = new Table(tableMetaInfo);

        for (int i = 1; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            List<Object> list = new ArrayList<>(lastCellNum);
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = row.getCell(j);
                list.add(j, getCellValue(cell));
            }
            table.insertItem(list);
        }
        return table;
    }

    @Override
    public Table readFromXlsFile(File file, int sheetNo) {
        return null;
    }

    @Override
    public Table readFromInsertSqlFile(File file) {
        FileReader fileReader;
        try {
            fileReader = new FileReader(file);
        } catch (FileNotFoundException e) {
            throw new ScriptException("");
        }

        BufferedReader bufferedReader = new BufferedReader(fileReader);

        List<String> insertSqlList = new ArrayList<>();
        try {
            String str;
            while ((str = bufferedReader.readLine()) != null) {
                insertSqlList.add(str);
            }
        } catch (IOException e) {
            throw new ScriptException("从文件读取数据时出错：" + file.getAbsolutePath());
        }

        return readFromInsertSqlList(insertSqlList);
    }

    @Override
    public Table readFromInsertSqlList(List<String> insertSqlList) {
        return null;
    }

}
